package com.wing.web.excel;

import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil<T> {
    private static BeanUtilsBean beanUtilsBean = new BeanUtilsBean();

    static {
        beanUtilsBean.getConvertUtils().register(new org.apache.commons.beanutils.converters.DateConverter(null), java.util.Date.class);
    }
    /**
     * 表头名字和对应所在第几列的下标，用于根据title取到对应的值
     */
    private final Map<String,Integer> title_to_index = new HashMap<>();
    /**
     * 所有带有ExcelIn注解的字段
     */
    private final List<Field> fields = new ArrayList<>();

    /**
     * 统计表格的行和列数量用来遍历表格
     */
    private  int firstCellNum = 0;
    private  int lastCellNum = 0;
    private  int firstRowNum = 0;
    private  int lastRowNum = 0;

    private String sheetName ;

    private XSSFSheet sheet ;

    public List<T> read(InputStream in , Class clazz) throws Exception {
        gatherAnnotationFields(clazz);
        configSheet(in);
        configHeader();
        List rList= null;
        try {
            rList = readContent(clazz);
        } catch (IllegalAccessException e) {
            throw new Exception(e);
        } catch (InstantiationException e) {
            throw new Exception(e);
        } catch (InvocationTargetException e) {
            throw new Exception(e);
        }
        return rList ;
    }

    private List readContent(Class clazz) throws IllegalAccessException, InstantiationException, InvocationTargetException {
        Object o = null ;
        XSSFRow row = null ;
        List<Object> rsList = new ArrayList<>();
        Object value = null ;
        for(int i = (firstRowNum+1);i<=lastRowNum;i++){
            o = clazz.newInstance();
            row = sheet.getRow(i);
            XSSFCell cell = null ;
            for (Field field : fields) {
                //根据注解中的title，取到表格中该列所对应的的值
                Integer column=title_to_index.get(field.getAnnotation(ExcelIn.class).title());
                boolean isTrim = field.getAnnotation(ExcelIn.class).isTrim();
                if(column==null){
                    continue;
                }
                //加上行是否为空的判断 避免空指针
                if(row == null){
                    continue;
                }
                cell = row.getCell(column);
                value = getCellValue(cell,field.getType(),isTrim) ;
                if(null != value && StringUtils.isNotBlank(value.toString())) {
                    beanUtilsBean.setProperty(o, field.getName(), value);
                }
            }
            rsList.add(o);
        }
        return rsList ;
    }

    private void configSheet(InputStream in) throws Exception {
        //HSSFWorkbook:只能创建97-03版本的Excel，即：以xls结尾的Excel
        // 想要导入xlsx结尾的Excel，用XSSFWorkbook
        try(XSSFWorkbook wb = new XSSFWorkbook(in)){
            getSheetByName(wb);
        } catch (FileNotFoundException e) {
            throw new Exception(e);
        } catch (IOException e) {
            throw new Exception(e);
        }
    }


    /**
     * 根据sheet获取对应的行列值，和表头对应的列值映射
     */
    private void configHeader(){
        this.firstRowNum = sheet.getFirstRowNum() ;
        this.lastRowNum = sheet.getLastRowNum() ;
        //第一行为表头，拿到表头对应的列值
        XSSFRow row = sheet.getRow(firstRowNum);
        this.firstCellNum = row.getFirstCellNum();
        this.lastCellNum = row.getLastCellNum();
        for (int i = firstCellNum;i<lastCellNum;i++){
            title_to_index.put(row.getCell(i).getStringCellValue(),i);
        }
    }

    /**
     * 根据sheet名称获取sheet
     * @param workbook
     * @return
     * @throws Exception
     */
    private void getSheetByName(XSSFWorkbook workbook) throws Exception {
        int sheetNumber = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetNumber; i++) {
            String name = workbook.getSheetName(i);
            if(StringUtils.equals(this.sheetName,name)) {
                this.sheet = workbook.getSheetAt(i);
                return;
            }
        }
        throw new Exception("excel中未找到名称为"+this.sheetName+"的sheet");
    }

    /**
     * 根据自定义注解，获取所要导入表格的sheet名称和需要导入的字段名称
     * @param clazz
     * @throws Exception
     */
    private void gatherAnnotationFields(Class clazz) throws Exception {
        if(!clazz.isAnnotationPresent(ExcelIn.class)){
            throw new Exception(clazz.getName()+"类上没有ExcelIn注解");
        }
        ExcelIn excelIn = (ExcelIn)clazz.getAnnotation(ExcelIn.class) ;
        this.sheetName = excelIn.sheetName();
        // 得到所有定义字段
        Field[] allFields = FieldUtils.getAllFields(clazz) ;
        // 得到所有field并存放到一个list中
        for (Field field : allFields)
        {
            if (field.isAnnotationPresent(ExcelIn.class))
            {
                fields.add(field);
            }
        }
        if( fields.isEmpty()){
            throw new Exception(clazz.getName()+"中没有ExcelIn注解字段");
        }
    }

    private Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        Object obj = null;
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case FORMULA:
                try {
                    obj = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    obj = numericToBigDecimal(cell);
                }
                break;
            case NUMERIC:
                obj = getNumericValue(cell);
                break;
            case STRING:
                String value = String.valueOf(cell.getStringCellValue());
                value = value.replace(" ", "");
                value = value.replace("\n", "");
                value = value.replace("\t", "");
                obj = value;
                break;
            default:
                break;
        }
        return obj;
    }

    private Object getCellValue(Cell cell,Class clazz) {
        if (cell == null) {
            return "";
        }
        Object obj = null;
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case FORMULA:
                try {
                    obj = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    obj = numericToBigDecimal(cell);
                }
                break;
            case NUMERIC:
                if(clazz.equals(String.class) ){
                    obj = getNumericValue(cell);
                    if (obj!=null){
                       // obj=Double.valueOf(obj.toString()).intValue();
                        obj = NumberToTextConverter.toText(cell.getNumericCellValue());
                    }
                }else{
                    obj = getNumericValue(cell);
                }
                break;
            case STRING:
                String value = String.valueOf(cell.getStringCellValue());
                value = value.replace(" ", "");
                value = value.replace("\n", "");
                value = value.replace("\t", "");
                obj = value;
                break;
            default:
                break;
        }
        return obj;
    }

    private Object getCellValue(Cell cell,Class clazz,boolean isTrim) {
        if (cell == null) {
            return "";
        }
        Object obj = null;
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case FORMULA:
                try {
                    obj = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    obj = numericToBigDecimal(cell);
                }
                break;
            case NUMERIC:
                if(clazz.equals(String.class) ){
                    obj = getNumericValue(cell);
                    if (obj!=null){
                        // obj=Double.valueOf(obj.toString()).intValue();
                        obj = NumberToTextConverter.toText(cell.getNumericCellValue());
                    }
                }else{
                    obj = getNumericValue(cell);
                }
                break;
            case STRING:
                String value = String.valueOf(cell.getStringCellValue());
                if(isTrim){
                    value = value.replace(" ", "");
                }
                value = value.replace("\n", "");
                value = value.replace("\t", "");
                obj = value;
                break;
            default:
                break;
        }
        return obj;
    }

    private Object getNumericValue(Cell cell){
        // 处理日期格式、时间格式
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return  cell.getDateCellValue();
        }else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            double value = cell.getNumericCellValue();
            return org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
        } else {
            return numericToBigDecimal(cell);
        }
    }

    private Object numericToBigDecimal(Cell cell) {
        String valueOf = String.valueOf(cell.getNumericCellValue());
        BigDecimal bd = new BigDecimal(valueOf);
        return bd;
    }
}
